using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
public abstract class CMSDAO<T>:IDisposable
{
	#region Variables
    private IDbConnection _connection;
    private System.Data.IDbTransaction _transection;
    private bool _IsUseTransection = true;
    private bool _IsOnTransection = false;
    private bool _IsDebugMode = false;
    #endregion

    #region Properties
    public bool IsAutoTransection
    {
        get
        {
            return this._IsUseTransection;
        }
        set
        {
            this._IsUseTransection = value;
        }
    }
    public bool IsDebugMode
    {
        get
        {
            return this._IsDebugMode;
        }
        set
        {
            this._IsDebugMode = value;
        }
    }
    #endregion

    #region Constructor-Destructor
    public CMSDAO()
    {
        this._IsDebugMode = DbConstants.IsOnDebugMode();
        //this.CreateConnection();
    }
    ~CMSDAO()
    {
        //this.Dispose();
    }
    #endregion

    #region Create ADO.Net objects
    protected IDbCommand CreateCommand(string SQL)
    {
        IDbCommand cmd = CreateCommand();
        cmd.CommandText = SQL;
        return cmd;
    }
    protected IDbCommand CreateCommand()
    {
        IDbCommand cmd = new System.Data.SqlClient.SqlCommand();
        return cmd;
    }
    protected IDbConnection CreateConnection()
    {
        if (this._connection == null)
        {
            this._connection = new System.Data.SqlClient.SqlConnection();
            this._connection.ConnectionString = DbConstants.ConnectionString;
        }
        if ((this._connection.State == ConnectionState.Closed) || (this._connection.State == ConnectionState.Broken))
        {
            try
            {
                this._connection.Open();
            }
            catch (Exception ex)
            {
                if (_IsDebugMode)
                {
                    throw (ex);
                }
            }
        }
        return this._connection;
    }
    protected IDbDataAdapter CreateAdapter()
    {
        System.Data.IDbDataAdapter adp = new System.Data.SqlClient.SqlDataAdapter();
        return adp;
    }
    protected void CreateCommandParameter(IDbCommand cmd, string ParameterName, object Value)
    {
        IDataParameter par;
        par = cmd.CreateParameter();
        par.ParameterName = ParameterName;
        par.Value = Value;
        cmd.Parameters.Add(par);
    }
    #endregion

    #region Dispose Connections
    protected void CloseConnection()
    {
        if (this._connection == null) return;
        if (this._IsOnTransection)
        {
            this.CommitTransaction();
        }
        if (this._connection.State == ConnectionState.Open)
            this._connection.Close();
    }
    public void Dispose()
    {
        this.CloseConnection();
        if (this._connection == null) return;
        this._connection.Dispose();
    }
    #endregion

    #region Transections
    public void CreateTransaction()
    {
        if (this._IsOnTransection) return;
        this._transection = this._connection.BeginTransaction();
        this._IsOnTransection = true;
    }
    public void RollBackTransaction()
    {
        this._transection.Rollback();
        this._IsOnTransection = false;
    }
    public void CommitTransaction()
    {
        this._transection.Commit();
        this._IsOnTransection = false;
    }
    #endregion

    #region Select functions
    protected abstract T MapObject(IDataReader reader);
    protected List<T> SelectList(string strSQL)
    {
        IDbCommand cmd = this.CreateCommand(strSQL);
        return this.SelectList(cmd);
    }
    protected List<T> SelectList(IDbCommand cmd)
    {
        this.CreateConnection();
        cmd.Connection = this._connection;
        List<T> list = new List<T>();
        IDataReader reader = null;
        if (this._IsUseTransection)
        {
            this.CreateTransaction();
            cmd.Transaction = this._transection;
        }
        try
        {
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                list.Add(MapObject(reader));
            }
            try
            {
                if (!reader.IsClosed) reader.Close();
            }
            catch
            {
            }
            if (this._IsUseTransection)
            {
                this.CommitTransaction();
            }
        }
        catch (Exception ex)
        {           
            try
            {

                if (!reader.IsClosed) reader.Close();
            }
            catch
            {
            }
            if (this._IsUseTransection)
            {
                this.RollBackTransaction();
            }
            if (_IsDebugMode)
            {
                throw (ex);
            }
        }
        finally
        {
            if (this._IsUseTransection)
            {
                this.CloseConnection();
            }
        }
        return list;
    }
    protected T Select(string strSQL)
    {
        IDbCommand cmd = this.CreateCommand(strSQL);
        return this.Select(cmd);
    }
    protected T Select(IDbCommand cmd)
    {
        this.CreateConnection();
        cmd.Connection = this._connection;
        if (this._IsUseTransection)
        {
            this.CreateTransaction();
            cmd.Transaction = this._transection;
        }
        bool successed = false;
        IDataReader reader = null;
        try
        {
            reader = cmd.ExecuteReader();
            reader.Read();
            successed = true;
        }
        catch(Exception ex)
        {
            successed = false;
            try
            {
                if (!reader.IsClosed) reader.Close();
            }
            catch
            {
            }
            if (this._IsUseTransection)
            {
                this.RollBackTransaction();
            }
            if (_IsDebugMode)
            {
                throw (ex);
            }
        }
        T o = this.MapObject(reader);
        if (successed)
        {
            if (!reader.IsClosed) reader.Close();
            if (this._IsUseTransection)
            {
                this.CommitTransaction();
            }
        }
        if (this._IsUseTransection)
        {
            this.CloseConnection();
        }
        return o;
    }
    protected DataSet SelectDS(string strSQL)
    {
        IDbCommand cmd = this.CreateCommand(strSQL);
        return this.SelectDS(cmd);
    }
    protected DataSet SelectDS(IDbCommand cmd)
    {
        DataSet dtSet = new DataSet();
        System.Data.IDbDataAdapter adp = this.CreateAdapter();
        this.CreateConnection();
        cmd.Connection = _connection;
        if (this._IsUseTransection)
        {
            this.CreateTransaction();
            cmd.Transaction = _transection;
        }
        try
        {
            adp.SelectCommand = cmd;
            adp.Fill(dtSet);
            if (this._IsUseTransection)
            {
                this.CommitTransaction();
            }
        }
        catch
        {
            if (this._IsUseTransection)
            {
                this.RollBackTransaction();
            }
        }
        finally
        {
            if (this._IsUseTransection)
            {
                this.CloseConnection();
            }
        }
        return dtSet;
    }
    #endregion

    #region Execute command
    protected bool ExcuteNoneQuery(string SQL)
    {
        IDbCommand cmd = this.CreateCommand(SQL);
        return this.ExcuteNoneQuery(cmd);
    }
    protected object ExecuteScalar(string SQL, ref bool resultout)
    {
        IDbCommand cmd = this.CreateCommand(SQL);
        return this.ExecuteScalar(cmd, ref resultout);
    }
    protected object ExecuteScalar(IDbCommand cmd, ref bool resultout)
    {
        object result = null;
        this.CreateConnection();
        cmd.Connection = this._connection;
        if (_IsUseTransection)
        {
            this.CreateTransaction();
            cmd.Transaction = this._transection;
        }
        try
        {
            result = cmd.ExecuteScalar();
            if (_IsUseTransection)
            {
                this.CommitTransaction();
            }
            resultout = true;
        }
        catch (Exception ex)
        {           
            if (_IsUseTransection)
            {
                this.RollBackTransaction();
            }
            if (_IsDebugMode)
            {
                throw (ex);
            }
            resultout = false;
        }
        finally
        {
            cmd.Connection = null;
            this.CloseConnection();
        }
        return result;
    }
    protected bool ExcuteNoneQuery(IDbCommand cmd)
    {
        bool result = false;
        this.CreateConnection();
        cmd.Connection = _connection;
        if (this._IsUseTransection)
        {
            this.CreateTransaction();
            cmd.Transaction = this._transection;
        }
        try
        {
            cmd.ExecuteNonQuery();
            if (this._IsUseTransection)
            {
                this.CommitTransaction();
            }           
            result = true;
        }
        catch (Exception ex)
        {            
            if (this._IsUseTransection)
            {
                this.RollBackTransaction();
            }
            if (_IsDebugMode)
            {
                throw (ex);
            }
        }
        finally
        {
            this.CloseConnection();
			cmd.Connection=null;
        }
        return result;
    }
    #endregion

    #region Command secured
    //Remove injection in string filter 
    protected string RemoveInjection(string strFilter)
    {
        string[] InjectionCodes = new string[] { "create table ", ".sysobjects ", "information_schema.", "@@version", "delete ", "drop table", "exec ", "+convert(", " convert(", "--" };
        foreach (string st in InjectionCodes)
        {
            strFilter = RemoveInjection(strFilter, st);
        }
        return strFilter;
    }
    private string RemoveInjection(string strFilter, string InjectionCode)
    {
        int i = 0;
        string st = strFilter.ToUpper();
        i = st.IndexOf(InjectionCode.ToUpper());
        while (i >= 0)
        {
            strFilter = strFilter.Remove(i, InjectionCode.Length);
            st = st.Remove(i, InjectionCode.Length);
            i = st.IndexOf(InjectionCode.ToUpper());
        }
        return strFilter;
    }
    #endregion
}
